-- =============================================
-- This stored procedure creates a unique directory name for a specified nav menu record
--
-- Modifications
-- 08/22/2003    E.Tatsui
-- =============================================

CREATE          PROCEDURE amsp_CMGetUniqueDirectoryName
  @InNavMenuID numeric,
  @InUpdate bit = 0,
  @OutDirectoryName varchar(255) OUTPUT
AS
BEGIN

  DECLARE
    @CategoryDepth integer,
    @ParentNavMenuID numeric,
    @DirectoryName varchar(255),
    @Title varchar(255),
    @RowNum integer,
    @WebsiteKey uniqueidentifier,
    @Counter integer,
    @Temp varchar(255),
    @Index int,
    @Ascii int

  SELECT @ParentNavMenuID = ParentNavMenuID,
         @CategoryDepth = CategoryDepth,
         @DirectoryName = DirectoryName,
         @Title = Title,
         @WebsiteKey = WebsiteKey
    FROM Nav_Menu WITH (NOLOCK)
   WHERE NavMenuID = @InNavMenuID

  -- If directory name is not provided, let's create it from name.
  IF @DirectoryName IS NULL BEGIN
    SET @DirectoryName = @Title
  END

  -- Remove special characters (Everything except for 0-9,a-z,A-Z,and "_")
  SET @Index = 0
  SET @Temp = ''
  WHILE @Index < = LEN(@DirectoryName) BEGIN
    SET @Index = @Index + 1
    SET @Ascii = ASCII(SUBSTRING(@DirectoryName,@Index,1))
    IF (@Ascii between 48 and 57 )
       or(@Ascii between 65 and 90)
       or(@Ascii = 95)
       or(@Ascii between 97 and 122)
     SET @Temp = @Temp + Substring(@DirectoryName,@Index,1)
  END
  SET @DirectoryName = @Temp

  SET @DirectoryName = Left(@DirectoryName,100)
  
  -- Now find out if this DirectoryName is unique.
  IF @CategoryDepth = 1 BEGIN

    -- For level ones, it needs to be unique regardless of nav item or content folder.
    SELECT @RowNum = COUNT(*)
      FROM Nav_Menu WITH (NOLOCK)
     WHERE DirectoryName = @DirectoryName
       AND WebsiteKey = @WebsiteKey
       AND CategoryDepth = 1
       AND NavMenuID <> @InNavMenuID

    -- If we find a record with the same directory name, let's append a number at the end.    
    SET @Counter = 0
    WHILE @RowNum > 0 BEGIN
      SET @Counter = @Counter + 1
      SELECT @RowNum = COUNT(*)
        FROM Nav_Menu WITH (NOLOCK)
       WHERE DirectoryName = @DirectoryName + Cast(@Counter as varchar(255))
         AND WebsiteKey = @WebsiteKey
         AND CategoryDepth = 1
         AND NavMenuID <> @InNavMenuID
      SET @Counter = @Counter + 1
    END    
    
    IF @Counter > 0
      SET @OutDirectoryName = @DirectoryName + Cast(@Counter as varchar(255))
    ELSE
      SET @OutDirectoryName = @DirectoryName
  END --Ends Level 1s.
  ELSE BEGIN -- For all others, it needs to be unique between sibilings.

    -- For level ones, it needs to be unique regardless of nav item or content folder.
    SELECT @RowNum = COUNT(*)
      FROM Nav_Menu WITH (NOLOCK)
     WHERE DirectoryName = @DirectoryName
       AND WebsiteKey = @WebsiteKey
       AND CategoryDepth = @CategoryDepth
       AND ParentNavMenuID = @ParentNavMenuID
       AND NavMenuID <> @InNavMenuID

    -- If we find a record with the same directory name, let's append a number at the end.    
    SET @Counter = 0
    WHILE @RowNum > 0 BEGIN
      SET @Counter = @Counter + 1
      SELECT @RowNum = COUNT(*)
        FROM Nav_Menu WITH (NOLOCK)
       WHERE DirectoryName = @DirectoryName + Cast(@Counter as varchar(255))
         AND WebsiteKey = @WebsiteKey
         AND CategoryDepth = @CategoryDepth
         AND ParentNavMenuID = @ParentNavMenuID
         AND NavMenuID <> @InNavMenuID
    END    
    
    IF @Counter > 0
      SET @OutDirectoryName = @DirectoryName + Cast(@Counter as varchar(255))
    ELSE
      SET @OutDirectoryName = @DirectoryName
  END -- Ends all other levels.

  -- If "Update" is 1, and the directory name changed, update it.
  IF @InUpdate = 1
    UPDATE Nav_Menu
       SET DirectoryName = @OutDirectoryName
     WHERE NavMenuID = @InNavMenuID

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetUniqueDirectoryName] TO [IMIS]
GO